package com.ywjh.inter.dao;

import com.mysql.jdbc.Statement;
import com.ywjh.inter.entity.CholesterolEntity;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

public class CholesterolDAO {
    /**
     * 添加胆固醇数据*
     * @param conn
     * @param entity
     * @return
     * @throws SQLException
     */
    public int insert(Connection conn, CholesterolEntity entity) throws SQLException {
        int id = -1;
        PreparedStatement ps = conn.prepareStatement(
                "INSERT INTO cholesterol (user_id, data, add_time, check_time,status,memo,data_source) VALUES (?,?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
        ps.setInt(1, entity.getUser_id());
        ps.setFloat(2, entity.getData());
        ps.setTimestamp(3, entity.getAdd_time());
        ps.setTimestamp(4, entity.getCheck_time());
        ps.setInt(5, entity.getStatus());
        ps.setString(6, entity.getMemo());
        ps.setString(7, entity.getData_source());
        if (ps.executeUpdate() > 0) {
            ResultSet rs = ps.getGeneratedKeys();
            if (rs.next()) {
                id = rs.getInt(1);
            }
        }
        return id;
    }

    /**
     * 查询胆固醇列表*
     * @param conn
     * @param userid
     * @param startTime
     * @param endTime
     * @return
     * @throws SQLException
     */
    public List<CholesterolEntity> query(Connection conn, int userid, String startTime, String endTime) throws SQLException {
        List<CholesterolEntity> list = new LinkedList<>();
        PreparedStatement ps = conn.prepareStatement("select * from cholesterol where date_format(check_time,'%y%m%d') >= date_format(?,'%y%m%d') and date_format(check_time,'%y%m%d') <= date_format(?,'%y%m%d') and user_id =? and status=1 order by check_time desc");
        ps.setString(1, startTime);
        ps.setString(2, endTime);
        ps.setInt(3, userid);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            list.add(fill(rs));
        }
        return list;
    }
    
    public List<CholesterolEntity> query(Connection conn, String users, String startTime, String endTime) throws SQLException {
        List<CholesterolEntity> list = new LinkedList<>();
        if (users == null || "".equals(users)) {
            return list;
        }
        PreparedStatement ps = conn.prepareStatement("select * from cholesterol where date_format(check_time,'%y%m%d') >= date_format(?,'%y%m%d') and date_format(check_time,'%y%m%d') <= date_format(?,'%y%m%d') and user_id in (?) and status=1 order by check_time desc");
        ps.setString(1, startTime);
        ps.setString(2, endTime);
        ps.setString(3, users);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            list.add(fill(rs));
        }
        return list;
    }

    private CholesterolEntity fill(ResultSet rs) throws SQLException {
        CholesterolEntity e=new CholesterolEntity();
        e.setId(rs.getInt("id"));
        e.setAdd_time(rs.getTimestamp("add_time"));
        e.setStatus(rs.getInt("status"));
        e.setUser_id(rs.getInt("user_id"));
        e.setCheck_time(rs.getTimestamp("check_time"));
        e.setMemo(rs.getString("memo"));
        e.setData_source(rs.getString("data_source"));
        e.setData(rs.getFloat("data"));
        return e;
    }


}

